package dnbW.uiW.queryW.visual;

import dnbW.uiW.queryW.modelW.ConditionNodeLeafObject;
import dnbW.dbW.DatabaseInfo;
import dnbW.dbW.PathExpression;
import dnbW.dbW.sucxentdbW.Identifier;
import java.sql.Connection;
import java.sql.Statement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.Enumeration;
import java.util.List;
import java.util.Set;
import javax.swing.tree.DefaultMutableTreeNode;
import javax.swing.tree.DefaultTreeModel;
import java.io.*;
import java.sql.PreparedStatement;
import dnbW.uiW.dbW.DatabaseController;
import java.util.HashMap;
import java.util.Map;
import java.util.Iterator;
import java.util.Set;
import java.util.HashSet;

/**
 *
 * @author Colin
 */
public class RetrieveFinalResultsTask{
  
    private String pathufinal;
    private DatabaseInfo contextdb = null;
    private PathExpression outputPath = null;
    
    public static Set<String> intersectionTableNames = new HashSet<String>();
    
    private boolean isIntersection = false;

    public RetrieveFinalResultsTask(DatabaseInfo contextdb,dnbW.dbW.PathExpression path){
        this.contextdb = contextdb;
        this.outputPath = path;
        this.pathufinal = "PATHUFINAL" + controller.Controller.newQueryID;
    }

    public DefaultMutableTreeNode getFinalResultTree() throws Exception {

            Connection dbConnection = contextdb.getDbConnection();
            Statement statement = dbConnection.createStatement();
            
            // truncate (clear) the table (without dropping the table)
            System.out.println("Truncate Table......................"+pathufinal);
            statement.execute("truncate table " + pathufinal);
            
            ConditionNodeLeafObject conditionNodeLeafObject = new ConditionNodeLeafObject();
            
            ResultSet result = statement.executeQuery("select DocId, AncestorDeweyGroup from " + IdentityPathSQLTask.tableName);
                
            while (result.next()) {
                   conditionNodeLeafObject.getIdentityPathMapper().add(new Identifier(result.getInt(1), result.getLong(2)));
            }
            
            Set<Identifier> identityPaths = conditionNodeLeafObject.getIdentityPathMapper();
            
            if(intersectionTableNames.size() > 1)
            {
                
                  isIntersection = true;  
                
                  String finalQuery = "";
                  
                  StringBuilder query = new StringBuilder();
                
                  Iterator it = intersectionTableNames.iterator();
                  
                  while(it.hasNext())
                  {
                    String tempTableName = (String) it.next();
                    
                    query.append("SELECT DocId,AncestorDeweyGroup FROM " + tempTableName + " INTERSECT ");
                  }
                  
                  int lastIndex = query.lastIndexOf("I");
                  
                  finalQuery = query.substring(0, lastIndex);
                  
                  System.out.println("FinalQuery = "+finalQuery);
                  
                  
                  
                    String query2 = "insert into " + pathufinal + "(DocId, AncestorDeweyGroup) VALUES(?,?)";
                    
                    System.out.println("Result Query = "+query2);
                    
                    PreparedStatement preparedStatement = null;
                    try {
                        preparedStatement = dbConnection.prepareStatement(query2);
                        dbConnection.setAutoCommit(false);
                        
                        ResultSet resultSet = statement.executeQuery(finalQuery);
                        
                        while(resultSet.next())
                          {
                              preparedStatement.setInt(1, resultSet.getInt(1));
                              preparedStatement.setLong(2, resultSet.getLong(2));
                              preparedStatement.addBatch();
                          }

                        preparedStatement.executeBatch();
                    } finally {
                        if (preparedStatement != null) 
                            preparedStatement.close();
                        dbConnection.setAutoCommit(true);
                    }
            }    
            else
            {
                isIntersection = false;
                
                if (!identityPaths.isEmpty()) {

                    String query = "insert into " + pathufinal + "(DocId, AncestorDeweyGroup) VALUES(?,?)";
                    PreparedStatement preparedStatement = null;
                    try {
                        preparedStatement = dbConnection.prepareStatement(query);
                        dbConnection.setAutoCommit(false);
                        for(Identifier element : identityPaths){
                            preparedStatement.setInt(1, element.getDocId());
                            preparedStatement.setLong(2, element.getAncestorDeweyGroup());
                            preparedStatement.addBatch();
                        }
                        preparedStatement.executeBatch();
                    } finally {
                        if (preparedStatement != null) 
                            preparedStatement.close();
                        dbConnection.setAutoCommit(true);
                    }
                }
            }
            //PathExpression outputPath = owner.getOutputPath();

            List<Integer> leafIdList = new ArrayList<Integer>();
            List<Integer> attributeIdList = new ArrayList<Integer>();

            if (outputPath.isAttribute()) {
                List<DefaultMutableTreeNode> fullPath = outputPath.getFullPath();
                PathExpression parentPath =
                        new PathExpression(fullPath.get(fullPath.size() - 2), outputPath.getContextDb());
                parentPath.setDataSource(outputPath.getDataSource());
                DatabaseController.getInstance().getPathId(parentPath, leafIdList,  null);
                DatabaseController.getInstance().getPathId(outputPath, null, attributeIdList);
            } else {
                DatabaseController.getInstance().getPathId(outputPath, leafIdList, attributeIdList);
            }
            System.out.println("Calling createQuery()");
            //String sqlQuery = this.getQuery(owner.getOutputPath(), leafIdList, attributeIdList);
            String sqlQuery = dnbW.dbW.sucxentdbW.FinalResultQueryCreator.createQuery(outputPath, this.pathufinal);
            
            System.out.println("Translated SQL Query: \n" + sqlQuery);

            int pvTempIndex = sqlQuery.indexOf("PVTemp");
            int semiColonIndex = sqlQuery.indexOf(";");

            if (pvTempIndex == -1) {
                contextdb.getEngine().execute(sqlQuery);
            } else {
                String query1 = "truncate table " + outputPath.getDataSource().getName() + "_PVTEMP";
                String query2 = sqlQuery.substring(0, semiColonIndex).trim();
                String query3 = sqlQuery.substring(semiColonIndex + 1);

                statement.execute(query1);
                statement.execute(query2);

                contextdb.getEngine().execute(query3);
            }
            
            DefaultMutableTreeNode root;

            root = contextdb.getEngine().getTree(controller.Controller.resultSelectedDataSource);
            
            if(isIntersection)
            {
                ResultSet resultCount = statement.executeQuery("select count(*) from " + pathufinal);
                resultCount.next();

                int count = resultCount.getInt(1);
                
                root.setUserObject(count + " " + root.toString());
            }    
            else    
            {
                root.setUserObject(identityPaths.size() + " " + root.toString());
            }

            return root;
        
    }

//    private void condTreeTraversal(DefaultMutableTreeNode t)
//    {
//        // this node map
//        Set<Identifier> thisSet = ((OperatorNodeObject)t.getUserObject()).getIdentityPathMapper();
//        thisSet.clear();
//        for(Enumeration em = t.children(); em.hasMoreElements();){
//            DefaultMutableTreeNode node = (DefaultMutableTreeNode) em.nextElement();
//
//            Set<Identifier> childMap = null;
//            if(node.getUserObject() instanceof ConditionNodeLeafObject)
//            {
//                ConditionNodeLeafObject leafObject = (ConditionNodeLeafObject) node.getUserObject();
//                childMap = leafObject.getIdentityPathMapper();
//            }
//            else
//            {
//                condTreeTraversal(node);
//                OperatorNodeObject opNode = (OperatorNodeObject) node.getUserObject();
//                childMap = opNode.getIdentityPathMapper();
//            }
//            System.out.println(node.getUserObject() + ":" + childMap.size());
//  
//            if(((OperatorNodeObject)t.getUserObject()).getOperator().equalsIgnoreCase("and"))
//            { // AND operator: intersection all child maps
//                if (t.getIndex(node) == 0) { // first child
//                    thisSet.addAll(childMap);
//                }
//                else { // other children
//                    thisSet.retainAll(childMap);
//                }
//            }
//            else // OR operator: union all child maps
//            {
//                thisSet.addAll(childMap);
//            }
//        }
//    }
//    
//    private String condTreeTraversalSQLStr(DefaultMutableTreeNode t) {
//        StringBuilder strBld = new StringBuilder();
//        for(Enumeration em = t.children(); em.hasMoreElements();) {
//            DefaultMutableTreeNode node = (DefaultMutableTreeNode) em.nextElement();
//            if (t.getIndex(node) != 0) { // non-first child
//                if(((OperatorNodeObject)t.getUserObject()).getOperator().equalsIgnoreCase("and")) { // AND operator: intersection all child maps
//                    strBld.append(" INTERSECT ");
//                } else {// OR operator: union all child maps
//                    strBld.append(" UNION ");
//                }
//            }
//            
//            if(node.getUserObject() instanceof ConditionNodeLeafObject) {
//                ConditionNodeLeafObject leafObject = (ConditionNodeLeafObject) node.getUserObject();
//                strBld.append(leafObject.isCombinedIdentityPathAvailable() ? leafObject.getCombinedIdentityPathTable() : leafObject.getIdentityPathTable());
//            } else {
//                strBld.append('(').append(condTreeTraversalSQLStr(node)).append(')');
//            }
//        }
//        return strBld.toString();
//    }
}
